package online.yangcloud.common.database;

import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import online.yangcloud.common.annotation.DataColumn;
import online.yangcloud.common.annotation.DataIndex;
import online.yangcloud.common.annotation.DataTable;
import online.yangcloud.common.common.AppConstants;
import online.yangcloud.common.model.business.database.EntityInfoBusiness;
import online.yangcloud.common.model.business.database.TableCreatorBusiness;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @author zhuboyang
 * @since 2023年09月03 17:13:20
 */
@Repository
public class MysqlMapper {

    private static final Logger logger = LoggerFactory.getLogger(MysqlMapper.class);

    @Resource
    private JdbcTemplate jdbcTemplate;

    public void runner(List<Object> tableNames) {
        // 检测实体类的信息，获取实体对应的表名，以及属性名
        List<EntityInfoBusiness> entities = new ArrayList<>();
        for (Object obj : tableNames) {
            DataTable tableInfo = obj.getClass().getAnnotation(DataTable.class);
            List<Field> fields = new ArrayList<>();
            fields.addAll(Arrays.asList(obj.getClass().getDeclaredFields()));
            fields.addAll(Arrays.asList(obj.getClass().getSuperclass().getDeclaredFields()));
            entities.add(EntityInfoBusiness.builder()
                    .setTableName(tableInfo.tableName())
                    .setTableDesc(tableInfo.desc())
                    .setClazz(obj)
                    .setFields(fields));
        }

        // 检测已存在的数据表
        List<String> names = acquireExistTables();

        // 创建数据表或填补缺失的属性字段
        for (EntityInfoBusiness info : entities) {
            // 数据表已存在
            if (names.contains(info.getTableName())) {
                // 获取已存在的字段
                List<String> columns = acquireExistColumns(info.getTableName());
                // 检测字段数量是否一致。如果一致，则没有缺失的字段
                if (info.getFields().size() == columns.size()) {
                    continue;
                }
                // 检测是否需要补充的字段。如果存在，则补充字段
                for (int i = 0; i < info.getFields().size(); i++) {
                    Field field = info.getFields().get(i);
                    DataColumn columnAnnotation = field.getAnnotation(DataColumn.class);
                    if (!columns.contains(columnAnnotation.name())) {
                        Field prev = null;
                        if (i > 0) {
                            prev = info.getFields().get(i - 1);
                        }
                        generateColumn(info.getTableName(), prev, columnAnnotation);
                    }
                }
                continue;
            }

            // 数据表不存在
            TableCreatorBusiness business = generateTable(info.getClazz().getClass());
            logger.info(
                    "Table [{}] has not been created yet and is being created for initialization",
                    info.getTableName()
            );
            jdbcTemplate.execute(business.getTableCreator());
        }
    }

    /**
     * 填补缺失的数据表字段
     *
     * @param tableName        数据表名
     * @param columnAnnotation 字段注解
     */
    private void generateColumn(String tableName, Field prev, DataColumn columnAnnotation) {
        StrBuilder builder = StrBuilder.create("alter table ")
                .append(tableName).append(" ")
                .append("add `").append(columnAnnotation.name()).append("` ");
        builder.append(columnAnnotation.type());
        if (AppConstants.Table.COLUMN_TYPES.contains(columnAnnotation.type())) {
            builder.append(AppConstants.Special.BRACKET_LEFT)
                    .append(columnAnnotation.length())
                    .append(AppConstants.Special.BRACKET_RIGHT);
        }
        builder.append(" ");
        if (columnAnnotation.canNull()) {
            builder.append("DEFAULT '").append(columnAnnotation.defaultValue()).append("' ");
        } else {
            builder.append("NOT NULL ");
        }
        builder.append("COMMENT '").append(columnAnnotation.comment()).append("' ");
        if (ObjUtil.isNotNull(prev)) {
            String prevName = prev.getAnnotation(DataColumn.class).name();
            builder.append("AFTER ").append(prevName).append(";");
        }
        logger.info(
                "Adding field 【{}】 to table 【{}】",
                columnAnnotation.name(),
                tableName
        );
        jdbcTemplate.execute(builder.toString());
    }

    /**
     * 查询数据表中已存在的字段
     *
     * @param tableName 数据表名
     * @return 字段列表
     */
    private List<String> acquireExistColumns(String tableName) {
        List<String> columns = new ArrayList<>();
        List<Map<String, Object>> list = jdbcTemplate.queryForList("show columns from " + tableName + "; ");
        list.forEach(o -> columns.add(StrUtil.toString(o.get("Field"))));
        return columns;
    }

    /**
     * 查询已存在的数据表
     *
     * @return 数据表
     */
    private List<String> acquireExistTables() {
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("show tables; ");
        List<String> tableNames = new ArrayList<>();
        maps.forEach(o -> o.forEach((k, v) -> tableNames.add(StrUtil.toString(v))));
        return tableNames;
    }

    /**
     * 生成‘用户表’的表结构 SQL
     *
     * @return 用户表 SQL
     */
    public TableCreatorBusiness generateTable(Class<?> clazz) {
        // 获取实体类的所有属性
        Field[] classFields = clazz.getDeclaredFields();

        // 获取继承的父类
        Class<?> superclass = clazz.getSuperclass();

        // 获取父类的所有属性
        Field[] superFields = superclass.getDeclaredFields();

        // 获取实体类对应表的信息
        DataTable tableAnnotation = clazz.getAnnotation(DataTable.class);

        // 表结构 SQL 构建器
        StrBuilder tableGenerator = StrBuilder.create("CREATE TABLE `").append(tableAnnotation.tableName()).append("` (\n");
        // 主键 SQL 构建器
        StrBuilder primaryGenerator = StrBuilder.create("  PRIMARY KEY (`");
        // 索引 SQL 构建器
        StrBuilder indexGenerator = StrBuilder.create();

        // 循环实体类的所有属性，构建 SQL
        generateTable(tableAnnotation.tableName(), tableGenerator, primaryGenerator, indexGenerator, classFields);
        // 循环父类的所有属性，构建 SQL
        generateTable(tableAnnotation.tableName(), tableGenerator, primaryGenerator, indexGenerator, superFields);

        // 整合 SQL 构建器
        tableGenerator.append(primaryGenerator);
        tableGenerator.append(indexGenerator.subString(0, indexGenerator.length() - 2)).append("\n");
        tableGenerator.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='")
                .append(tableAnnotation.desc())
                .append("'\n");
        return TableCreatorBusiness.builder()
                .setTableCreator(tableGenerator.toString());
    }

    private void generateTable(String name, StrBuilder table, StrBuilder primary, StrBuilder index, Field[] fields) {
        for (Field field : fields) {
            DataColumn columnAnnotation = field.getAnnotation(DataColumn.class);
            // 字段名
            table.append("  `").append(columnAnnotation.name()).append("` ");
            // 字段类型
            table.append(columnAnnotation.type());
            if (AppConstants.Table.COLUMN_TYPES.contains(columnAnnotation.type())) {
                table.append("(").append(columnAnnotation.length()).append(")");
            }
            table.append(" ");
            // 字段编码格式
            if (AppConstants.Table.COLUMN_TYPES.contains(columnAnnotation.type())) {
                table.append("CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ");
            }
            // 是否可为空
            if (columnAnnotation.canNull()) {
                table.append("DEFAULT '").append(columnAnnotation.defaultValue()).append("' ");
            } else {
                table.append("NOT NULL ");
            }
            // 备注
            table.append("COMMENT '").append(columnAnnotation.comment()).append("',\n");

            // 字段是否为主键
            if (columnAnnotation.primary()) {
                primary.append(columnAnnotation.name()).append("`),\n");
            }
            // 是否设置索引
            DataIndex indexAnnotation = field.getAnnotation(DataIndex.class);
            if (ObjectUtil.isNotNull(indexAnnotation)) {
                // 是否是唯一索引
                index.append("  ");
                if (indexAnnotation.unique()) {
                    index.append("UNIQUE ");
                }
                index.append("KEY ")
                        .append("`").append(name).append("_").append(columnAnnotation.name()).append("_index").append("` ")
                        .append("(`").append(columnAnnotation.name()).append("`),\n");
            }
        }
    }

}
